/**
 * @Author: SevDaisy十七散人 
 * @Date: 2020-07-07 09:42:47
 */
package zyy.control;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import zyy.model.BeanOrderProjects;
import zyy.model.BeanProduct;
import zyy.util.BusinessException;
import zyy.util.DBUtil;
import zyy.util.DbException;
import zyy.util.Bus;

public class OrderProjectsManager {
  public List<BeanOrderProjects> loadAll(int OrderID) throws DbException {
    List<BeanOrderProjects> result = new ArrayList<BeanOrderProjects>();
    Connection conn = null;
    try {
      System.out.println("reload OrderProjectsManager is Called");
      conn = DBUtil.getConnection();
      String sql = "SELECT pName,opCnt,opSum,opDiscount,MID,mName,ProductID from OrderProjects WHERE OrderID=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, OrderID);
      java.sql.ResultSet rs = pst.executeQuery();
      while (rs.next()) {
        BeanOrderProjects p = new BeanOrderProjects();
        p.setOrderID(OrderID);
        p.setpName(rs.getString(1));
        p.setOpCnt(rs.getDouble(2));
        p.setOpSum(rs.getDouble(3));
        p.setOpDiscount(rs.getDouble(4));
        p.setMID(rs.getInt(5));
        p.setmName(rs.getString(6));
        p.setProductID(rs.getInt(7));
        result.add(p);
        System.out.println(rs.getString(1) + rs.getString(2) + rs.getString(3) + rs.getString(4));
      }
      rs.close();
      pst.close();
    } catch (SQLException e) {
      e.printStackTrace();
      throw new DbException(e);
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
    return result;
  }

  public BeanOrderProjects add_1(BeanProduct x) throws BusinessException {
    BeanOrderProjects result = null;
    // 预备 添加 商品 x.getProductID()
    // 到订单 Link.currentOrder (其实这个Order可以让FrmUserGo传参过来)
    // 先查，当前订单有没有这个商品
    // select opCnt from OrderProjects where OrderID=? and ProductID=?
    // 如果没有，直接 add 1 到 orderProject，
    // 确认库存，增加库存限制
    // 如果有，保存 opCnt 的值 +1 然后和 x.getRealvalue 等运算，更新 OrderProjects 的这个元组数据
    // 最后 更新当前 order 的数据，用 update 语句
    int OrderID = Bus.currentOrder.getOrderID();
    int ProductID = x.getProductID();
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      conn.setAutoCommit(false);
      String sql = "select opCnt,opSum from OrderProjects where OrderID=? and ProductID=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, OrderID);
      pst.setInt(2, ProductID);
      java.sql.ResultSet rs = pst.executeQuery();
      double opCnt;
      double opSum;
      // double store; // 结算才对库存Update
      if (rs.next()) {
        opCnt = rs.getDouble(1) + 1;
        opSum = rs.getDouble(2) + x.getpRealValue();
        sql = "SELECT p.store FROM Product p WHERE ProductID=?";
        pst = conn.prepareStatement(sql);
        pst.setInt(1, ProductID);
        rs = pst.executeQuery();
        if (rs.next()) {
          // store = rs.getDouble(1);
          if (opCnt > rs.getDouble(1)) {
            throw new BusinessException("库存不够，订单更新失败");
          } else {
            System.out.printf("update Order:%d opCnt:%.2f opSum:%.2f\n", OrderID, opCnt, opSum);
            sql = "update OrderProjects set opCnt=?,opSum=? where OrderID=? and ProductID=?";
            pst = conn.prepareStatement(sql);
            pst.setDouble(1, opCnt);
            pst.setDouble(2, opSum);
            pst.setInt(3, OrderID);
            pst.setInt(4, ProductID);
            pst.executeUpdate();
          }
        } else {
          throw new BusinessException("查无此商品 ID:" + ProductID);
        }
      } else {
        opCnt = 1;
        opSum = x.getpRealValue();
        sql = "SELECT p.store FROM Product p WHERE ProductID=?";
        PreparedStatement pst1 = conn.prepareStatement(sql);
        pst1.setInt(1, ProductID);
        ResultSet rs1 = pst1.executeQuery();
        if (rs1.next()) {
          // store = rs1.getDouble(1);
          if (opCnt > rs1.getDouble(1)) {
            throw new BusinessException("库存不够，订单更新失败");
          } else {
            System.out.printf("insert new OrderProjects OrderID:%d ProductID:%d pName:%s opCnt:%.2f opSum:%.2f\n",
                OrderID, ProductID, x.getpName(), opCnt, opSum);
            sql = "insert into OrderProjects(OrderID,ProductID,pName,opCnt,opSum,opDiscount,MID,mName) "
                + " values(?,?,?,?,?,?,?,?)";
            PreparedStatement pst2 = conn.prepareStatement(sql);
            pst2.setInt(1, OrderID);
            pst2.setInt(2, ProductID);
            pst2.setString(3, x.getpName());
            pst2.setDouble(4, opCnt);
            pst2.setDouble(5, opSum);
            pst2.setDouble(6, x.getpValue() - x.getpRealValue());
            pst2.setInt(7, x.getMID());
            pst2.setString(8, x.getmName());
            pst2.executeUpdate();
            pst2.close();
          }
        } else {
          throw new BusinessException("查无此商品 ID:" + ProductID);
        }
        pst1.close();
        rs1.close();
      }
      double oValue = 0, oRealValue = 0;
      sql = "select oValue,oRealValue from orders where OrderID=?";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, OrderID);
      rs = pst.executeQuery();
      if (rs.next()) {
        oValue = rs.getDouble(1);
        oRealValue = rs.getDouble(2);
      } else {
        System.out.println("啥都没有？啥情况啊");
      }
      oValue += x.getpValue();
      oRealValue += x.getpRealValue();
      sql = "update orders set oValue=?,oRealValue=?,planTime=? where OrderID=?";
      pst = conn.prepareStatement(sql);
      pst.setDouble(1, oValue);
      pst.setDouble(2, oRealValue);
      pst.setTimestamp(3, new java.sql.Timestamp(System.currentTimeMillis()));
      pst.setInt(4, OrderID);
      pst.executeUpdate();
      // sql = "UPDATE Product SET store=? WHERE ProductID=?";
      // pst = conn.prepareStatement(sql);
      // pst.setDouble(1, store - opCnt);
      // pst.setInt(2, ProductID);
      // pst.executeUpdate();
      rs.close();
      pst.close();
      conn.commit();
    } catch (SQLException e) {
      System.out.println("出现SQL异常，开始回滚");
      try {
        conn.rollback();
        System.out.println("回滚成功");
      } catch (SQLException e1) {
        System.out.println("回滚失败！！！");
        throw new BusinessException(e1.getMessage());
      }
      e.printStackTrace();
      throw new BusinessException(e.getMessage());
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
    return result;
  }

  public BeanOrderProjects unadd_1(BeanProduct x) throws DbException, BusinessException {
    BeanOrderProjects result = null;
    // 大致同添加
    // 不同的是，如果发现是零，应该主动删除这个项
    // 有另一种写法：不用 currentProduct 而是用 currentOrderProduct
    // 但是不想用，嘿嘿嘿
    // 重点Bug：删除时使用了默认选择的0-0-0商品。
    int OrderID = Bus.currentOrder.getOrderID();
    int ProductID = x.getProductID();
    Connection conn = null;
    try {
      conn = DBUtil.getConnection();
      conn.setAutoCommit(false);
      String sql = "select opCnt,opSum from OrderProjects where OrderID=? and ProductID=?";
      java.sql.PreparedStatement pst = conn.prepareStatement(sql);
      pst.setInt(1, OrderID);
      pst.setInt(2, ProductID);
      java.sql.ResultSet rs = pst.executeQuery();
      double opCnt;
      double opSum;
      if (rs.next()) {
        opCnt = rs.getDouble(1);
        opSum = rs.getDouble(2);
        if (opCnt <= 1) {
          System.out.printf("delete Order:%d opCnt:%.2f opSum:%.2f\n", OrderID, opCnt, opSum);
          sql = "delete from OrderProjects where OrderID=? and ProductID=?";
          pst = conn.prepareStatement(sql);
          pst.setInt(1, OrderID);
          pst.setInt(2, ProductID);
          pst.executeUpdate();
        } else {
          System.out.printf("update(minus) Order:%d opCnt:%.2f opSum:%.2f\n", OrderID, opCnt, opSum);
          sql = "update OrderProjects set opCnt=?,opSum=? where OrderID=? and ProductID=?";
          PreparedStatement pst1 = conn.prepareStatement(sql);
          pst1.setDouble(1, opCnt - 1);
          pst1.setDouble(2, opSum - x.getpRealValue());
          pst1.setInt(3, OrderID);
          pst1.setInt(4, ProductID);
          pst1.executeUpdate();
        }
      } else {
        String msg = String.format("数据库本订单中无此商品 %s:%s，请检查是否正确选择了商品", x.getmName(), x.getpName());
        throw new BusinessException(msg);
      }
      double oValue = 0, oRealValue = 0;
      sql = "select oValue,oRealValue from orders where OrderID=?";
      pst = conn.prepareStatement(sql);
      pst.setInt(1, OrderID);
      rs = pst.executeQuery();
      if (rs.next()) {
        oValue = rs.getDouble(1);
        oRealValue = rs.getDouble(2);
      } else {
        System.out.println("啥都没有？啥情况啊");
      }
      oValue -= x.getpValue();
      oRealValue -= x.getpRealValue();
      if (opCnt <= 0) {
        oRealValue = oValue = 0;
      }
      sql = "update orders set oValue=?,oRealValue=?,planTime=? where OrderID=?";
      pst = conn.prepareStatement(sql);
      pst.setDouble(1, oValue);
      pst.setDouble(2, oRealValue);
      pst.setTimestamp(3, new java.sql.Timestamp(System.currentTimeMillis()));
      pst.setInt(4, OrderID);
      pst.executeUpdate();
      rs.close();
      pst.close();
      conn.commit();
    } catch (SQLException e) {
      System.out.println("出现SQL异常，开始回滚");
      try {
        conn.rollback();
        System.out.println("回滚成功");
      } catch (SQLException e1) {
        System.out.println("回滚失败！！！");
        throw new DbException(e1);
      }
      e.printStackTrace();
      throw new DbException(e);
    } finally {
      if (conn != null)
        try {
          conn.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
    }
    return result;
  }
}